-----动态部分 
with shift as (
--例如1：（08:00-17:00），则补全为（0:00-07:59）\（08:00-17:00）\（17:01-23:59）
--例如2：（08:00-16:00）（16:01-22:30），则补全为 （0:00-07:59）\（08:00-16:00）（16:01-22:30）和（22:31-23:59）
    select
        network_code,start_hm_2 as start_hm,end_hm_2 as end_hm
    from
        (
            select
                network_code,start_hm,end_hm,start_hm_arr
                 ,case when ARRAY_CONTAINS(start_hm_arr,start_hm)!=1 and start_hm='00:00' then '00:00'
                       when ARRAY_CONTAINS(start_hm_arr,start_hm)!=1 then from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',start_hm,':59'))+60,'HH:mm')
                       else start_hm
                       end as start_hm_2
                 ,case when ARRAY_CONTAINS(start_hm_arr,end_hm)=1  then from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',end_hm,':59'))-60,'HH:mm')
                  else end_hm
           end as end_hm_2
            from
                (
                SELECT
                network_code,start_hm
                    ,collect_set(if(flag=1,start_hm,null)) over(partition by network_code) as start_hm_arr
                    ,lead(start_hm,1,'23:59') over(partition by network_code order by start_hm) as end_hm
                from
                (
                SELECT
                network_code,start_hm,flag,
                row_number() over(partition by network_code,start_hm order by flag) as rk
                from
                (
                select network_code,receipt_start_time as start_hm, 1 as flag
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1 and dt ='{{ execution_date | cst_ds }}' and receipt_start_time<receipt_end_time
                union all
                select network_code,receipt_end_time  as start_hm , 2 as flag
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1 and dt ='{{ execution_date | cst_ds }}' and receipt_start_time<receipt_end_time
                union all
                select network_code,'00:00' as start_hm , 3 as flag
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1 and dt ='{{ execution_date | cst_ds }}' and receipt_start_time<receipt_end_time
                union all
                select network_code,'23:59' as start_hm , 3 as flag
                from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1 and dt ='{{ execution_date | cst_ds }}' and receipt_start_time<receipt_end_time
                )t --where network_code='8971107'
                )t2 where rk=1
                )t3 where start_hm!=end_hm
        )t4 where start_hm_2<=end_hm_2
),
     shipment_line as (
         select /*+ broadcast(s) */
             s.shipment_no,e.line_code,e.shift
         from
             (
                 select shipment_no,vehicleline_code ,vehicleline_id ,vehicleline_name,create_time
                 from jms_dwd.dwd_tmsnew_shipment_union_base_dt
                 where dt between date_add('{{ execution_date | cst_ds }}',-25) and '{{ execution_date | cst_ds }}'
                   and table_type =2
                   and shipment_state!=5
   and vehicleline_code is not null
             )s left join
             (
                 select
                     line_code,shift,effective_date,expiration_date,dt
                 from jms_dim.dim_tab_tmsnew_time_effective_base_dt
                 where dt between date_add('{{ execution_date | cst_ds }}',-25) and '{{ execution_date | cst_ds }}'
                   and is_enable =1
             )e  on s.vehicleline_code=e.line_code and date(s.create_time) =e.dt and s.create_time between e.effective_date and e.expiration_date
where e.shift is not null
    ),
    tab_hm_net_line_bill_cnt as (
select /*+ broadcast(s1) */
    w2.is_last       --是否近7日
        , w2.taking_code   --揽收网点
        , w2.dispatch_code --派件网点
        , s1.start_hm      --开始时段
        , s1.end_hm        --开始时段
        , w2.line_str      --线路code
        , w2.line_str_name --线路code
        , w2.aging_days    --时效
        , w2.shift        --散货班次
        , count(1) as  bill_cnt --单量
from
    (
    select /*+ broadcast(sl) */
    w1.waybill_no
        , w1.taking_time
        , w1.taking_code
        , w1.dispatch_code
        , REGEXP_REPLACE(concat_ws('-',sort_array(collect_list(w1.step_site))),'\\d_','') as line_str   ---站点排序_站点code 将`站点排序_`替换为‘’
        , REGEXP_REPLACE(concat_ws('-',sort_array(collect_list(w1.step_site_name))),'\\d_','') as line_str_name
        , w1.is_last
        , datediff(w1.sign_time,w1.taking_time) as aging_days
        , sl.shift
    from
    (
    select
    w.waybill_no
        , w.taking_early_scantime     as taking_time
        , w.taking_early_scansitecode as taking_code
        , w.send_station_network_code as scan_site_code
        , w.send_station_number       as step_number
        , w.dispatch_network_code     as dispatch_code
        , w.send_station_network_type
        , concat(w.send_station_number,'_',w.send_station_network_code) as step_site
        , concat(w.send_station_number,'_',nvl(n3.name,'无')) as step_site_name
        , if(w.dt between date_add('{{ execution_date | cst_ds }}',-6) and '{{ execution_date | cst_ds }}' ,1,0) as is_last
        , coalesce(w.instore_early_scantime,w.sign_early_scantime,w.outstore_early_scantime) as sign_time
    --, if(trim(end_network_unloading_arrival_shipment_no)='',null,end_network_unloading_arrival_shipment_no) as end_arrival_shipment_no1  --末端网点到件任务号
    --, if(next_station_network_code=dispatch_network_code,send_station_loading_send_later_shipment_no,null) as  end_arrival_shipment_no
        , max(if(next_station_network_code=dispatch_network_code,send_station_loading_send_later_shipment_no,null)) over(partition by w.waybill_no) as end_arrival_shipment_no
    from jms_dwd.dwd_wide_sign_trace_waybill_step_dt w
    left join jms_dim.dim_network_whole_massage n3 on n3.code =w.send_station_network_code
    where w.dt between date_add('{{ execution_date | cst_ds }}',-13) and '{{ execution_date | cst_ds }}'
    --剔除脏数据
    and w.is_refund=0 and w.is_void=0 --退转件、作废件
    and w.taking_early_scansitecode is not null and w.dispatch_network_code is not null
    --and nvl(w.send_station_network_type,-99)!=3 --过滤集散点
    and w.taking_early_scantime < coalesce(w.instore_early_scantime,w.sign_early_scantime,w.outstore_early_scantime)
    --and taking_early_scansitecode ='1020100'--in ('4579181','4579117')
    --and dispatch_network_code ='1769396'--in ('8931118','6357153')
    --义乌荷叶塘网点 4579181
    --兰州桃林路网点 8931118
    --and taking_code='1020100'
    --and dispatch_code='1769396'
    )w1 inner join shipment_line sl on sl.shipment_no=w1.end_arrival_shipment_no
    where nvl(w1.send_station_network_type,-99)!=3
    group by w1.waybill_no,w1.taking_code,w1.dispatch_code,w1.taking_time,w1.is_last,w1.sign_time,sl.shift
    )w2
    ---存在始发网点没有揽收班次的情况，取交集
    inner join shift s1 on date_format(w2.taking_time,'HH:mm') between s1.start_hm  and s1.end_hm and s1.network_code=w2.taking_code
group by s1.start_hm,s1.end_hm,w2.is_last,w2.taking_code,w2.dispatch_code,w2.line_str,w2.line_str_name,w2.aging_days,w2.shift
    )


insert overwrite table jms_tmp.tmp_cainiao_active_tn_history_dt partition(dt)
select
    taking_code     --揽收网点
     , taking_name
     , dispatch_code --派件网点
     , dispatch_name
     , start_hm      --开始时段
     , end_hm        --开始时段
     , line_str      --线路code
     , aging_days    --时效天数
     , line_cnt         --(始末网点,揽收时段,线路,时效)单量
     , days_cnt   as aging_days_cnt      --(始末网点,揽收时段,时效)单量
     , taking_hm_cnt    --(始末网点,揽收时段)单量
     , se_network_total --始末网点单量
     , null as bill_total       --基数总单量
     , row_number() over(partition by taking_code,dispatch_code,start_hm,end_hm order by days_cnt desc,line_cnt desc,is_main_route asc,line_shift_cnt desc,site_number asc,line_str asc) as sort_num
     , current_timestamp() as update_time --更新时间
     , line_str_name
     , is_within_shift --是否班次内
     , shift as in_shift_number  --散货班次
     , line_shift_cnt
     , '{{ execution_date | cst_ds }}' as dt
from
    (
        select
            taking_code   --揽收网点
             , n1.name     as  taking_name
             , dispatch_code --派件网点
             , n2.name     as  dispatch_name
             , start_hm      --开始时段
             , end_hm        --开始时段
             , line_str      --线路code
             , line_str_name
             , aging_days
             , sum(line_cnt) over(partition by taking_code,dispatch_code,start_hm,aging_days,line_str) as line_cnt  --(始末网点,揽收时段,线路,时效,散货班次)单量
             , sum(line_cnt) over(partition by taking_code,dispatch_code,start_hm,aging_days) as days_cnt    --(始末网点,揽收时段,时效,散货班次)单量
             , sum(line_cnt) over(partition by taking_code,dispatch_code,start_hm) as taking_hm_cnt    --(始末网点,揽收时段)单量
             , sum(line_cnt) over(partition by taking_code,dispatch_code)          as se_network_total --始末网点单量
             --, sum(line_cnt) over() as bill_total                                                      --总单量
             , if(r.route_code is not null,0,1) is_main_route --是否是主用路由0是1否
             , size(split(line_str,'-')) as site_number --节点数
             , shift         --散货班次
             , if(shi.receipt_end_time>=end_hm,1,0)  as is_within_shift
             , line_cnt as line_shift_cnt --(始末网点,揽收时段,时效,线路)单量
        from
            (
            select
            taking_code   --揽收网点
                , dispatch_code --派件网点
                , start_hm      --开始时段
                , end_hm        --开始时段
                , line_str      --线路code
                , line_str_name
                , aging_days
                , shift
                , sum(bill_cnt) as line_cnt --(始末网点,揽收时段,线路,时效,散货班次)单量
            from  --单量少于100用近14天数据计算，大于100近7天计算
            (
            select
            is_last       --是否近7日
                , taking_code   --揽收网点
                , dispatch_code --派件网点
                , start_hm      --开始时段
                , end_hm        --开始时段
                , line_str      --线路code
                , line_str_name
                , bill_cnt      --单量
                , aging_days
                , shift
                , if((sum(if(is_last=1,bill_cnt,0)) over(partition by taking_code,dispatch_code,is_last))<100,14,7)  as  use_days  --始末网点总单量
            from tab_hm_net_line_bill_cnt
            )ud  where(use_days=14 and is_last in (0,1)) or (use_days=7 and is_last=1)
            group by   taking_code   --揽收网点
                , dispatch_code --派件网点
                , start_hm      --开始时段
                , end_hm        --开始时段
                , line_str      --线路code
                , line_str_name
                , aging_days
                , shift
            )hnl1 left join jms_dim.dim_yl_tms_route_base_dt r on r.dt='{{ execution_date | cst_ds }}'and r.status = 1 and r.route_type=1 and r.route_code=regexp_extract(hnl1.line_str,'-(.*)-')
            left join jms_dim.dim_network_whole_massage n1 on n1.code =hnl1.taking_code
            left join jms_dim.dim_network_whole_massage n2 on n2.code =hnl1.dispatch_code
            left join ( select network_code,max(receipt_end_time) as receipt_end_time --最晚收件时段
            from jms_dim.dim_yl_tmsnew_tms_in_warehouse_shi_base_dt
            where STATUS = 1 and IS_DELETE = 1 and IS_ENABLE=1 and dt ='{{ execution_date | cst_ds }}'
            group by network_code ) shi on shi.network_code=hnl1.taking_code
    )fm distribute by dt,abs(hash(concat(taking_code,dispatch_code)))%80;
-----------------------------------------------------------------------------------------------------------------------------
--------动态部分关联静态数据
insert overwrite table jms_tmp.tmp_cainiao_active_tn_match_dt partition(dt)
select
    taking_code   --揽收网点code
     , taking_name   --揽收网点name
     , deliver_code  --派件网点code
     , deliver_name  --派件网点name
     , start_hm      --开始时段
     , end_hm        --开始时段
     , line_str      --线路code(全程)
     , aging_days    --时效天数
     , out_sign_days --静态时效天
     , is_within_shift --是否班次内 
     , detail_route_id --中心线路动态(全程)
     , center_line_s   --中心线路静态(全程)
     , is_main_route   --是否主用路由 1 3主用0备用
     , route_id        --路由 id 
     , line_weight     --线路排序权重
     , line_rn
     , in_shift_num
     , shift_weight
     , out_last_shift_code --动态散货班次
     , '{{ execution_date | cst_ds }}' as dt
from
    (
        select
            taking_code   --揽收网点code
             , taking_name   --揽收网点name
             , deliver_code  --派件网点code
             , deliver_name  --派件网点name
             , start_hm      --开始时段
             , end_hm        --开始时段
             , line_str      --线路code
             , aging_days    --时效天数
             , out_sign_days --静态时效天
             , is_within_shift --是否班次内
             , center_line_s   --中心线路静态
             , is_main_route   --是否主用路由 1 3主用0备用
             , route_id        --路由 id
             , line_weight     --线路排序权重
             , detail_route_id
             , out_last_shift_code
             , in_shift_num
             , shift_weight
             --取静态最优中心线路
             , row_number()  over(partition by taking_code,deliver_code,start_hm,end_hm,out_sign_days order by is_line_match asc, shift_weight asc,line_weight asc ,out_last_shift_code desc,latest_warehousing_time_quantum desc) as line_rn
        from
            (
                select
                    dd.taking_code   --揽收网点code
                     , dd.taking_name   --揽收网点name
                     , dd.deliver_code  --派件网点code
                     , dd.deliver_name  --派件网点name
                     , dd.start_hm      --开始时段
                     , dd.end_hm        --开始时段
                     , dd.line_str      --线路code
                     , dd.aging_days    --时效天数
                     , dd.out_sign_days --静态时效天
                     , dd.is_within_shift --是否班次内
                     , stc2.center_line_s --中心线路静态
                     , stc2.is_main_route --是否主用路由 1 3主用0备用
                     , stc2.route_id      --路由 id
                     , stc2.center_site_num
                     , stc2.detail_route_id
                     , stc2.latest_warehousing_time_quantum  --揽收班次 '00:00-18:00'
                     , stc2.out_last_shift_code              --散货班次 1,2..
                     , dd.in_shift_num
                     --线路是否匹配上
                     , case when dd.line_str = stc2.center_line_s then 0 else 1 end as is_line_match
                     , case --班次匹配优先级
                           when dd.in_shift_num = stc2.out_last_shift_code and dd.taking_time_interval = stc2.latest_warehousing_time_quantum  then 1 --揽收班次和散货班次完全匹配
                           when dd.in_shift_num = stc2.out_last_shift_code then 2 --散货班次匹配，取最晚揽收班次
                           when dd.taking_time_interval = stc2.latest_warehousing_time_quantum then 3 --揽收班次批次
                           else 4                                                 --无对应散货标识，则取最晚的散货标识中的最后一个揽收班次
                    end as shift_weight --班次权重
                     ---中心路线的优先级打标
                     , case --线路匹配上的
                           when dd.line_str = stc2.center_line_s and is_within_shift=1 and is_true='T' then 1 --揽收班次内只取真实
                           when dd.line_str = stc2.center_line_s and is_within_shift=0 and is_true='F' then 1 --揽收班次外优先取虚拟
                           when dd.line_str = stc2.center_line_s and is_within_shift=0 and is_true='T' then 2 --揽收班次外优先取虚拟次取真实
                    --线路没有匹配上的
                           when dd.line_str != stc2.center_line_s and is_within_shift=1 and is_true='T' then 3 --揽收班次内优先取真实次取虚拟
                          when dd.line_str != stc2.center_line_s and is_within_shift=0 and is_true='F' then 3 --揽收班次外优先取虚拟次取真实
                          else 99 end as line_weight --线路权重
                from
                    (
                    select
                    dnc.taking_code   --揽收网点code
                        , dnc.taking_name   --揽收网点name
                        , dnc.deliver_code  --派件网点code
                        , dnc.deliver_name  --派件网点name
                        , dnc.start_hm      --开始时段
                        , dnc.end_hm        --开始时段
                        , dnc.line_str      --线路code
                        , dnc.aging_days    --时效天数
                        , dnc.is_within_shift --是否班次内
                        , stc.out_sign_days --时效天
                    --取静态最优时效天
                        , row_number() over(partition by taking_code,deliver_code,start_hm,end_hm order by abs(aging_days-out_sign_days),out_sign_days) as days_rn
                        , concat(dnc.start_hm,'-',dnc.end_hm) as taking_time_interval --揽收时段
                        , in_shift_num  --散货班次
                    from
                    (
                    select
                    taking_code   --揽收网点
                        , taking_name
                        , dispatch_code as deliver_code --派件网点
                        , dispatch_name as deliver_name
                        , start_hm      --开始时段
                        , end_hm        --开始时段
                        , line_str      --线路code
                        , aging_days    --时效天数
                        , is_within_shift
                        , in_shift_num  --散货班次
                    from jms_tmp.tmp_cainiao_active_tn_history_dt
                    where dt='{{ execution_date | cst_ds }}' and sort_number=1 --and taking_code='8029355'
                    )dnc left join
                    ( --取静态最优时效天
                    select
                    in_from_code  --始发网点
                        , out_to_code  --目的网点
                        , out_sign_days --时效天
                    from jms_dm.dm_cainiao_route_push_all_data_dt
                    where dt='{{ execution_date | cst_ds }}' --and in_from_code='8029355'
                    group by
                    in_from_code  --始发网点
                        , out_to_code  --目的网点
                        , out_sign_days --时效天
                    )stc on dnc.taking_code=stc.in_from_code and dnc.deliver_code=stc.out_to_code
                    )dd inner join
                    ( --取静态最优中心线路
                    select
                    in_from_code  --始发网点
                        , out_to_code  --目的网点
                        , is_main_route --是否主用1 3主用0备用
                        , out_sign_days --时效天
                        , route_id
                        , detail_route_id
                        , concat_ws('-',in_from_code,e1_code,e2_code,e3_code,e4_code,e5_code,e6_code,e7_code,out_to_code) as center_line_s
                        , size(split(concat_ws('-',in_from_code,e1_code,e2_code,e3_code,e4_code,e5_code,e6_code,e7_code,out_to_code),'-')) as center_site_num
                    --, regexp_extract(detail_route_id,'-(.*)-',1) as center_line_s  --中心线路
                    --, size(split(regexp_extract(detail_route_id,'-(.*)-',1),'-')) as center_site_num
                        , is_true --T真F虚拟
                        , latest_warehousing_time_quantum  --揽收班次 '00:00-18:00'
                        , out_last_shift_code              --散货班次 1,2..
                    from jms_dm.dm_cainiao_route_push_all_data_dt
                    where dt='{{ execution_date | cst_ds }}'  --and in_from_code='8029355'
                    )stc2 on dd.taking_code=stc2.in_from_code and dd.deliver_code=stc2.out_to_code and dd.out_sign_days=stc2.out_sign_days
                    and dd.days_rn=1
            )t )tt --where line_rn =1 放在下面处理了
    distribute by dt,abs(hash(concat(taking_code,deliver_code)))%80;

-----------------------------------------------------------------------------------------------------------------------------------
--补数据
with shift_all as (
--例如1：（08:00-17:00），则补全为（0:00-07:59）\（08:00-17:00）\（17:01-23:59）
--例如2：（08:00-16:00）（16:01-22:30），则补全为 （0:00-07:59）\（08:00-16:00）（16:01-22:30）和（22:31-23:59）
    select
        taking_code,deliver_code,start_hm_2 as start_hm,end_hm_2 as end_hm
    from
        (
            select
                taking_code,deliver_code,start_hm,end_hm,start_hm_arr
                 ,case when ARRAY_CONTAINS(start_hm_arr,start_hm)!=1 and start_hm='00:00' then '00:00' 
                      when ARRAY_CONTAINS(start_hm_arr,start_hm)!=1 then from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',start_hm,':59'))+60,'HH:mm')
                 else start_hm
                 end as start_hm_2
                ,case when ARRAY_CONTAINS(start_hm_arr,end_hm)=1  then from_unixtime(unix_timestamp(concat('{{ execution_date | cst_ds }}',' ',end_hm,':59'))-60,'HH:mm')
                 else end_hm
                 end as end_hm_2
            from
                (
                SELECT
                taking_code,deliver_code,start_hm
                    ,collect_set(if(flag=1,start_hm,null)) over(partition by taking_code,deliver_code) as start_hm_arr
                    ,lead(start_hm,1,'23:59') over(partition by taking_code,deliver_code order by start_hm) as end_hm
                from
                (
                SELECT
                taking_code,deliver_code,start_hm,flag,
                row_number() over(partition by taking_code,deliver_code,start_hm order by flag) as rk
                from
                (
                select taking_code,deliver_code,start_hm, 1 as flag from jms_tmp.tmp_cainiao_active_tn_match_dt  where dt='{{ execution_date | cst_ds }}' and line_rn=1
                union all
                select taking_code,deliver_code,end_hm  , 2 as flag from jms_tmp.tmp_cainiao_active_tn_match_dt  where dt='{{ execution_date | cst_ds }}' and line_rn=1
                union all
                select taking_code,deliver_code,'00:00' , 3 as flag from jms_tmp.tmp_cainiao_active_tn_match_dt  where dt='{{ execution_date | cst_ds }}' and line_rn=1
                union all
                select taking_code,deliver_code,'23:59' , 3 as flag from jms_tmp.tmp_cainiao_active_tn_match_dt  where dt='{{ execution_date | cst_ds }}' and line_rn=1
                )t
                )t2 where rk=1
                )t3 where start_hm!=end_hm
        )t4 where start_hm_2<=end_hm_2
)

---insert overwrite table bidefault.dm_cainiao_active_tn_all_dt
insert overwrite table jms_dm.dm_cainiao_active_tn_v2_dt partition(dt)
select
    taking_code
     , taking_name
     , deliver_code
     , deliver_name
     , start_hm
     , end_hm
     , cast(out_sign_days as int) as days
     , '{{ execution_date | cst_ds }}' as date_time
     , null as rt
     , route_id
     , is_bushu
     , detail_route_id
     , '{{ execution_date | cst_ds }}' as  dt
from
    (
        select --时段短缺，取就近班次时效最快的,对应的id
            taking_code
             , taking_name
             , deliver_code
             , deliver_name
             , start_hm
             , end_hm
             , coalesce(detail_route_id,preceding_first_id_detail,following_first_id_detail) as detail_route_id
             , coalesce(route_id,preceding_first_id,following_first_id) as route_id
             , if(route_id is null,1,0) as is_bushu -----1 补充 ,2始末网点无发货补
             , coalesce(out_sign_days,preceding_first_id_days,following_first_id_days) as out_sign_days
        from
            (
                select
                    shift.taking_code
                     , net1.name as taking_name
                     , shift.deliver_code
                     , net2.name as deliver_name
                     , shift.start_hm
                     , shift.end_hm
                     --跳过空值：窗口无上边界到当前行,获取最近的那条线路id（揽收时段没有线路id时）
                     , first_value(mth.route_id,true)        over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between unbounded preceding and current row ) as preceding_first_id
        , first_value(mth.detail_route_id,true) over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between unbounded preceding and current row ) as preceding_first_id_detail
        , first_value(mth.out_sign_days,true)   over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between unbounded preceding and current row ) as preceding_first_id_days

        --跳过空值：当前行到无下边界,获取最近的那条线路id(揽收时段没有线路id时）
        , first_value(mth.route_id,true)        over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between current row and  unbounded following ) as following_first_id
        , first_value(mth.detail_route_id,true) over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between current row and  unbounded following ) as following_first_id_detail
        , first_value(mth.out_sign_days,true)   over(partition by shift.taking_code,shift.deliver_code  order by shift.start_hm rows between current row and  unbounded following ) as following_first_id_days
        , mth.route_id
                     , mth.detail_route_id
                     , mth.out_sign_days
                from shift_all shift
                         left join jms_tmp.tmp_cainiao_active_tn_match_dt mth
                                   on  mth.dt='{{ execution_date | cst_ds }}'  and mth.line_rn=1
                                       and shift.taking_code=mth.taking_code and shift.deliver_code=mth.deliver_code
                                       and shift.start_hm=mth.start_hm  and shift.end_hm=mth.end_hm
                         left join jms_dim.dim_network_whole_massage net1 on net1.code=shift.taking_code
                         left join jms_dim.dim_network_whole_massage net2 on net2.code=shift.deliver_code
            )t

        union all

        select
            in_from_code as taking_code  --始发网点
             , in_from_name as taking_name  --始发网点名称
             , out_to_code  as deliver_code --目的网点
             , out_to_name  as deliver_name --目的网点名称
             , '00:00' as start_hm
             , '23:59' as end_hm
             , detail_route_id
             , route_id   --路由id 
             , 2 as is_bushu --2 无发货
             , out_sign_days
        from
            (
                select
                    in_from_code  --始发网点
                     , in_from_name
                     , out_to_code  --目的网点
                     , out_to_name
                     , is_main_route --是否主用1 3主用0备用
                     , out_sign_days --时效天
                     , route_id
                     , detail_route_id
                     , concat_ws('-',in_from_code,e1_code,e2_code,e3_code,e4_code,e5_code,e6_code,e7_code,out_to_code) as center_line_s
                     , size(split(concat_ws('-',in_from_code,e1_code,e2_code,e3_code,e4_code,e5_code,e6_code,e7_code,out_to_code),'-')) as center_site_num
                     , is_true --T真F虚拟
                     , row_number() over(partition by in_from_code,out_to_code order by out_sign_days,size(split(concat_ws('-',in_from_code,e1_code,e2_code,e3_code,e4_code,e5_code,e6_code,e7_code,out_to_code),'-')),detail_route_id) as static_route_rn  --时效最快,节点最少
                from jms_dm.dm_cainiao_route_push_all_data_dt stc
                    left join ( select taking_code , deliver_code from jms_tmp.tmp_cainiao_active_tn_match_dt where dt='{{ execution_date | cst_ds }}' group by taking_code , deliver_code )mth
                on mth.taking_code=stc.in_from_code and mth.deliver_code=stc.out_to_code
                where mth.taking_code is null and stc.is_main_route in (1,3) --只取主用路由
                  and stc.dt='{{ execution_date | cst_ds }}'
            )t where t.static_route_rn=1
    )tt  distribute by dt,abs(hash(route_id))%999;


